
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 07/19/2012 15:29:33
-- Generated from EDMX file: C:\Users\Michael\Documents\Visual Studio 2012\Projects\ExaPlanning\Domaine\ExaPlanning.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ExaPlanning];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ActiviteCollaborateur]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Collaborator] DROP CONSTRAINT [FK_ActiviteCollaborateur];
GO
IF OBJECT_ID(N'[dbo].[FK_PosteCollaborateur]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Collaborator] DROP CONSTRAINT [FK_PosteCollaborateur];
GO
IF OBJECT_ID(N'[dbo].[FK_LocalisationCollaborateur]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Collaborator] DROP CONSTRAINT [FK_LocalisationCollaborateur];
GO
IF OBJECT_ID(N'[dbo].[FK_StateProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Project] DROP CONSTRAINT [FK_StateProject];
GO
IF OBJECT_ID(N'[dbo].[FK_TaskCollaborator]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Task] DROP CONSTRAINT [FK_TaskCollaborator];
GO
IF OBJECT_ID(N'[dbo].[FK_TaskProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Task] DROP CONSTRAINT [FK_TaskProject];
GO
IF OBJECT_ID(N'[dbo].[FK_TaskTaskType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Task] DROP CONSTRAINT [FK_TaskTaskType];
GO
IF OBJECT_ID(N'[dbo].[FK_Team_Collaborator]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Team] DROP CONSTRAINT [FK_Team_Collaborator];
GO
IF OBJECT_ID(N'[dbo].[FK_Team_Project]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Team] DROP CONSTRAINT [FK_Team_Project];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Collaborator]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Collaborator];
GO
IF OBJECT_ID(N'[dbo].[Activity]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Activity];
GO
IF OBJECT_ID(N'[dbo].[Job]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Job];
GO
IF OBJECT_ID(N'[dbo].[Location]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Location];
GO
IF OBJECT_ID(N'[dbo].[Project]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Project];
GO
IF OBJECT_ID(N'[dbo].[State]', 'U') IS NOT NULL
    DROP TABLE [dbo].[State];
GO
IF OBJECT_ID(N'[dbo].[Task]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Task];
GO
IF OBJECT_ID(N'[dbo].[TaskType]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TaskType];
GO
IF OBJECT_ID(N'[dbo].[Team]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Team];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Collaborator'
CREATE TABLE [dbo].[Collaborator] (
    [Name] nvarchar(max)  NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [MobilePhone] nvarchar(max)  NOT NULL,
    [Login] nvarchar(255)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [URLImage] nvarchar(max)  NOT NULL,
    [ModificationDate] datetime  NOT NULL,
    [IsActive] bit  NOT NULL,
    [Activity_ID] int  NOT NULL,
    [Job_ID] int  NOT NULL,
    [Location_ID] int  NOT NULL
);
GO

-- Creating table 'Activity'
CREATE TABLE [dbo].[Activity] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Job'
CREATE TABLE [dbo].[Job] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Location'
CREATE TABLE [dbo].[Location] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Project'
CREATE TABLE [dbo].[Project] (
    [Reference] nvarchar(255)  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Client] nvarchar(max)  NOT NULL,
    [BeginDate] datetime  NOT NULL,
    [EndDate] datetime  NOT NULL,
    [ColorCode] nvarchar(max)  NOT NULL,
    [State_ID] int  NOT NULL
);
GO

-- Creating table 'State'
CREATE TABLE [dbo].[State] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Task'
CREATE TABLE [dbo].[Task] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [BeginDate] datetime  NOT NULL,
    [EndDate] datetime  NOT NULL,
    [TJM] int  NULL,
    [Collaborator_Login] nvarchar(255)  NOT NULL,
    [Project_Reference] nvarchar(255)  NULL,
    [TaskType_ID] int  NOT NULL
);
GO

-- Creating table 'TaskType'
CREATE TABLE [dbo].[TaskType] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Team'
CREATE TABLE [dbo].[Team] (
    [CollaboratorSet_Login] nvarchar(255)  NOT NULL,
    [ProjectSet_Reference] nvarchar(255)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Login] in table 'Collaborator'
ALTER TABLE [dbo].[Collaborator]
ADD CONSTRAINT [PK_Collaborator]
    PRIMARY KEY CLUSTERED ([Login] ASC);
GO

-- Creating primary key on [ID] in table 'Activity'
ALTER TABLE [dbo].[Activity]
ADD CONSTRAINT [PK_Activity]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Job'
ALTER TABLE [dbo].[Job]
ADD CONSTRAINT [PK_Job]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Location'
ALTER TABLE [dbo].[Location]
ADD CONSTRAINT [PK_Location]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [Reference] in table 'Project'
ALTER TABLE [dbo].[Project]
ADD CONSTRAINT [PK_Project]
    PRIMARY KEY CLUSTERED ([Reference] ASC);
GO

-- Creating primary key on [ID] in table 'State'
ALTER TABLE [dbo].[State]
ADD CONSTRAINT [PK_State]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Task'
ALTER TABLE [dbo].[Task]
ADD CONSTRAINT [PK_Task]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'TaskType'
ALTER TABLE [dbo].[TaskType]
ADD CONSTRAINT [PK_TaskType]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [CollaboratorSet_Login], [ProjectSet_Reference] in table 'Team'
ALTER TABLE [dbo].[Team]
ADD CONSTRAINT [PK_Team]
    PRIMARY KEY NONCLUSTERED ([CollaboratorSet_Login], [ProjectSet_Reference] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Activity_ID] in table 'Collaborator'
ALTER TABLE [dbo].[Collaborator]
ADD CONSTRAINT [FK_ActiviteCollaborateur]
    FOREIGN KEY ([Activity_ID])
    REFERENCES [dbo].[Activity]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ActiviteCollaborateur'
CREATE INDEX [IX_FK_ActiviteCollaborateur]
ON [dbo].[Collaborator]
    ([Activity_ID]);
GO

-- Creating foreign key on [Job_ID] in table 'Collaborator'
ALTER TABLE [dbo].[Collaborator]
ADD CONSTRAINT [FK_PosteCollaborateur]
    FOREIGN KEY ([Job_ID])
    REFERENCES [dbo].[Job]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PosteCollaborateur'
CREATE INDEX [IX_FK_PosteCollaborateur]
ON [dbo].[Collaborator]
    ([Job_ID]);
GO

-- Creating foreign key on [Location_ID] in table 'Collaborator'
ALTER TABLE [dbo].[Collaborator]
ADD CONSTRAINT [FK_LocalisationCollaborateur]
    FOREIGN KEY ([Location_ID])
    REFERENCES [dbo].[Location]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_LocalisationCollaborateur'
CREATE INDEX [IX_FK_LocalisationCollaborateur]
ON [dbo].[Collaborator]
    ([Location_ID]);
GO

-- Creating foreign key on [State_ID] in table 'Project'
ALTER TABLE [dbo].[Project]
ADD CONSTRAINT [FK_StateProject]
    FOREIGN KEY ([State_ID])
    REFERENCES [dbo].[State]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StateProject'
CREATE INDEX [IX_FK_StateProject]
ON [dbo].[Project]
    ([State_ID]);
GO

-- Creating foreign key on [Collaborator_Login] in table 'Task'
ALTER TABLE [dbo].[Task]
ADD CONSTRAINT [FK_TaskCollaborator]
    FOREIGN KEY ([Collaborator_Login])
    REFERENCES [dbo].[Collaborator]
        ([Login])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskCollaborator'
CREATE INDEX [IX_FK_TaskCollaborator]
ON [dbo].[Task]
    ([Collaborator_Login]);
GO

-- Creating foreign key on [Project_Reference] in table 'Task'
ALTER TABLE [dbo].[Task]
ADD CONSTRAINT [FK_TaskProject]
    FOREIGN KEY ([Project_Reference])
    REFERENCES [dbo].[Project]
        ([Reference])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskProject'
CREATE INDEX [IX_FK_TaskProject]
ON [dbo].[Task]
    ([Project_Reference]);
GO

-- Creating foreign key on [TaskType_ID] in table 'Task'
ALTER TABLE [dbo].[Task]
ADD CONSTRAINT [FK_TaskTaskType]
    FOREIGN KEY ([TaskType_ID])
    REFERENCES [dbo].[TaskType]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskTaskType'
CREATE INDEX [IX_FK_TaskTaskType]
ON [dbo].[Task]
    ([TaskType_ID]);
GO

-- Creating foreign key on [CollaboratorSet_Login] in table 'Team'
ALTER TABLE [dbo].[Team]
ADD CONSTRAINT [FK_Team_Collaborator]
    FOREIGN KEY ([CollaboratorSet_Login])
    REFERENCES [dbo].[Collaborator]
        ([Login])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [ProjectSet_Reference] in table 'Team'
ALTER TABLE [dbo].[Team]
ADD CONSTRAINT [FK_Team_Project]
    FOREIGN KEY ([ProjectSet_Reference])
    REFERENCES [dbo].[Project]
        ([Reference])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Team_Project'
CREATE INDEX [IX_FK_Team_Project]
ON [dbo].[Team]
    ([ProjectSet_Reference]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------